﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KSSModel;
using Umbraco.Core;
using Umbraco.Core.Models;
using Umbraco.Core.Services;
using umbraco.MacroEngines;
using System.Web.Script.Serialization;

namespace KSSOnline.Controller
{
    public class kssForm
    {
        public static List<IContent> GetKssFormItem()
        {

            var contentService = ApplicationContext.Current.Services.ContentService;

            var ns = contentService.GetChildren(1057);
            return ns.ToList();
        }

        public static DynamicNodeList GetKssFormDynamic()
        {
            var d = new DynamicNode(1057);
            return d.Descendants();
        }
        public static DynamicNodeList GetKssFormApprove1Dynamic()
        {
            var d = new DynamicNode(1095);
            return d.Descendants();
        }
        public static List<tblDoc> GetDocByID(string docId)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    var doc = (from tagcate in dataContext.tblDocs
                               where tagcate.id == docId
                               select tagcate).ToList();

                    var aView = doc;
                    return aView;
                }
                catch (Exception ex)
                {
                    return null;
                }

            }
        }


        public static List<TblKDoc> GetDocByUsername(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    if (string.IsNullOrEmpty(username))
                    {
                        var doc = (from docs in dataContext.tblDocs
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                              {
                                                  approved1Date = docs.approved1Date,
                                                  approved2Date = docs.approved1Date,
                                                  approvedQMDate = docs.approvedQMDate,
                                                  approver1 = u.FirstName + " " + u.Lastname,
                                                  approver2 = u2.FirstName + " " + u2.Lastname,
                                                  cash = docs.cash,
                                                  createdBy = docs.createdBy,
                                                  createdByName = docs.createdByName,
                                                  createdDate = docs.createdDate,
                                                  autoId = docs.autoId,
                                                  departmentName = docs.departmentName,
                                                  doctypeId = docs.doctypeId,
                                                  id = docs.id,
                                                  status = docs.status,
                                                  score = docs.score,
                                                  title = docs.title,
                                                  tqm = docs.tqm
                                              }).ToList();

                        var aView = doc;
                        return aView;
                    }
                    else
                    {
                        var doc = (from docs in dataContext.tblDocs
                                   where docs.createdBy == username
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                       
                                   }).ToList();

                        var aView = doc;
                        return aView;
                    }
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }
            }
        }

        public static List<TblKDoc> GetDocByUsername(string username, int status)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    if (!string.IsNullOrEmpty(username))
                    {
                        var doc = (from docs in dataContext.tblDocs
                                   where docs.createdBy == username && docs.status == status
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();

                        var aView = doc;
                        return aView;
                    }
                    return null;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }

            }
        }

        public static List<TblKDoc> GetDocByUsername(string username, int status, string tag)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    List<TblKDoc> doc;
                    if (!string.IsNullOrEmpty(tag))
                    {
                        string[] tags = tag.Split(':');
                        if (tags.Count() > 1)
                        {
                            doc = (from docs in dataContext.tblDocs
                                       join tbltag in dataContext.tblDocTags on docs.id equals tbltag.docID
                                   where docs.createdBy == username && docs.status == status && tbltag.tagCateID == Convert.ToInt32(tags[0]) && tbltag.tagId == Convert.ToInt32(tags[1])
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();
                        }
                        else
                        {
                            doc = (from docs in dataContext.tblDocs
                                       join tbltag in dataContext.tblDocTags on docs.id equals  tbltag.docID
                                       where docs.createdBy == username && docs.status == status && tbltag.tagCateID == Convert.ToInt32(tags[0])
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();
                        }
                        

                        var aView = doc;
                        return aView;
                    }
                    return null;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }

            }
        }
        public static List<TblKDoc> GetDocByUsername(string username, string tag)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                try
                {
                    List<TblKDoc> doc;
                    if (!string.IsNullOrEmpty(tag))
                    {
                        string[] tags = tag.Split(':');
                        if (tags.Count() > 1)
                        {
                            doc = (from docs in dataContext.tblDocs
                                   join tbltag in dataContext.tblDocTags on docs.id equals tbltag.docID
                                   where docs.createdBy == username && tbltag.tagCateID == Convert.ToInt32(tags[0]) && tbltag.tagId == Convert.ToInt32(tags[1])
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();
                        }
                        else
                        {
                            doc = (from docs in dataContext.tblDocs
                                   join tbltag in dataContext.tblDocTags on docs.id equals tbltag.docID
                                   where docs.createdBy == username  && tbltag.tagCateID == Convert.ToInt32(tags[0])
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();
                        }


                        var aView = doc;
                        return aView;
                    }
                    return null;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return null;
                }

            }
        }

        public static List<ReportRy> GetReportRy(int month, SearchCiteria searchCiteria)
        {
            using (var dc = new KSSDBDataContext())
            {
                var allDoc = from doc in dc.tblDocs
                             select doc;
                var allhr = from doc in dc.tblEDMSHRs
                             select doc;
                var allSec = from k in dc.tblSections
                             
                             select k;
               
                if (!string.IsNullOrEmpty(searchCiteria.Location) && searchCiteria.Location.ToLower() != "all" )
                {
                    allhr = allhr.Where(hr => hr.mlocation == searchCiteria.Location.ToLower());
                    allSec = allSec.Where(s => s.location == searchCiteria.Location.ToLower());
                }
                if (!string.IsNullOrEmpty(searchCiteria.CreatedByName))
                {
                    allDoc = allDoc.Where(d => d.createdByName.Contains(searchCiteria.CreatedByName));
                }
                if (!string.IsNullOrEmpty(searchCiteria.CreatedBy))
                {
                    allDoc = allDoc.Where(d => d.createdBy.Contains(searchCiteria.CreatedBy));
                }
                if (!string.IsNullOrEmpty(searchCiteria.DepartmentName))
                {
                    allDoc = allDoc.Where(d => d.departmentName.Contains(searchCiteria.DepartmentName));
                }
                if (!string.IsNullOrEmpty(searchCiteria.Title))
                {
                    allDoc = allDoc.Where(d => d.title.Contains(searchCiteria.Title));
                }
                if (!string.IsNullOrEmpty(searchCiteria.Cash))
                {
                    decimal? cash = Convert.ToDecimal(searchCiteria.Cash);
                    allDoc = allDoc.Where(d => d.cash == cash);
                }

                if (searchCiteria.Datefrom.HasValue && searchCiteria.Datefrom.Value > DateTime.MinValue)
                {
                    allDoc = allDoc.Where(p => p.createdDate >= searchCiteria.Datefrom);
                }

                if (searchCiteria.Dateto.HasValue && searchCiteria.Dateto.Value > DateTime.MinValue)
                {
                    allDoc = allDoc.Where(p => p.createdDate < searchCiteria.Dateto);
                }
                
                

                var result = from doc in allDoc
                             join emp in allhr on doc.createdBy equals emp.mstaffno
                             join dp in dc.docPropertyDatas on doc.id equals dp.docId
                             where dp.propertyName == "savecost"
                             group new {emp, doc, dp} by new {emp.sacnameTh}
                             into g
                             select new
                                        {
                                            g.Key.sacnameTh,
                                            Doccount = g.Count(i => i.emp.mstaffno != null),
                                            Cash = g.Sum(i => i.doc.cash),
                                            moneySave = g.Sum(i => Convert.ToInt32(i.dp.dataNvarchar))
                                        };
                if (month == -1)
                {
                    month = System.DateTime.Today.Month;
                }
                var resultThisMonth = from doc in allDoc
                                      join emp in allhr on doc.createdBy equals emp.mstaffno
                                join dp in dc.docPropertyDatas on doc.id equals dp.docId
                                where dp.propertyName == "savecost" && doc.createdDate.Month == month
                                group new { emp, doc, dp } by new { emp.sacnameTh }
                                    into g
                                    select new
                                    {
                                        g.Key.sacnameTh,
                                        DoccountThisMonth = g.Count(i => i.emp.mstaffno != null),
                                        CashThisMonth = g.Sum(i => i.doc.cash),
                                        moneySaveThisMonth = g.Sum(i => Convert.ToInt32(i.dp.dataNvarchar))
                                    };

                var resultByEmp = from a in
                                      (from doc in allDoc
                                             join emp in allhr on doc.createdBy equals emp.mstaffno
                                  join dp in dc.docPropertyDatas on doc.id equals dp.docId
                                  where dp.propertyName == "savecost"
                                  group new {emp, doc, dp} by new {emp.sacnameTh, emp.mstaffno}
                                  into g
                                  select new
                                             {
                                                 g.Key.sacnameTh,
                                                 Count = g.Count(i => i.emp.mstaffno != null),
                                                 g.Key.mstaffno,
                                                 Cash = g.Sum(i => i.doc.cash),
                                                 MoneySave = g.Sum(i => Convert.ToInt32(i.dp.dataNvarchar))
                                             })
                                             group a by a.sacnameTh
                                  into g2
                                  select new
                                             {
                                                 sacnameTh = g2.Key,
                                                 EMPCount = g2.Count(i => i.mstaffno != null),
                                                 Cash = g2.Sum(i => i.Cash),
                                                 MoneySave = g2.Sum(i => i.MoneySave)

                                             };

                var resultByEmpThisMonth = from a in
                                               (from doc in allDoc
                                                join emp in allhr on doc.createdBy equals emp.mstaffno
                                       join dp in dc.docPropertyDatas on doc.id equals dp.docId
                                       where dp.propertyName == "savecost" && doc.createdDate.Month == DateTime.Today.Month
                                       group new { emp, doc, dp } by new { emp.sacnameTh, emp.mstaffno }
                                           into g
                                           select new
                                           {
                                               g.Key.sacnameTh,
                                               Count = g.Count(i => i.emp.mstaffno != null),
                                               g.Key.mstaffno,
                                               Cash = g.Sum(i => i.doc.cash),
                                               MoneySave = g.Sum(i => Convert.ToInt32(i.dp.dataNvarchar))
                                           })
                                  group a by a.sacnameTh
                                      into g2
                                      select new
                                      {
                                          sacnameTh = g2.Key,
                                          EMPCountThisMonth = g2.Count(i => i.mstaffno != null),
                                      };              

                
                        

                var k2 = (from r in result
                          join r2 in resultByEmp on r.sacnameTh equals r2.sacnameTh
                          join r3 in resultThisMonth on r.sacnameTh equals r3.sacnameTh
                          join r4 in resultByEmpThisMonth on r.sacnameTh equals r4.sacnameTh
                         select new ReportRy
                                    {
                                        sacnameTh = r.sacnameTh,
                                        moneySave = r.moneySave,
                                        Cash = r.Cash,
                                        Doccount = r.Doccount,
                                        EMPCount = r2.EMPCount,
                                        DoccountThisMonth = r3.DoccountThisMonth,
                                        EMPCountThisMonth = r4.EMPCountThisMonth,
                                        CashThisMonth = r3.CashThisMonth,
                                        moneySaveThisMonth = r3.moneySaveThisMonth
                                    }).ToList<ReportRy>();

                foreach (var tblSection in allSec)
                {
                    if (k2.Any(i => i.sacnameTh != tblSection.sacnameTh))
                    {
                        k2.Add(new ReportRy()
                        {
                            sacnameTh = tblSection.sacnameTh,
                        });
                    }
                }
                return k2;

            }
        }

        public static List<ReportRYTechno> GetReportRyTechnology(int month, SearchCiteria searchCiteria)
        {
            using (var dc = new KSSDBDataContext())
            {

                List<ReportRYTechno> result = dc.ExecuteQuery<ReportRYTechno>(@"SELECT  COUNT(*) AS Doccount, r.mstaffno AS Mstaffno, r.sacnameTh AS SacnameTh, DATEPART(MONTH, d.createdDate) as month, DATEPART(YEAR, d.createdDate) AS year, r.mtitleTh AS MTitle, r.mnameTh AS MName, r.surnameTh AS MSurname, 
                         r.memail as MEmail
FROM            tblEDMSHR AS r INNER JOIN
                         tblDoc AS d ON r.mstaffno = d.createdBy
WHERE        (r.divnameTh LIKE N'%ฝ่ายพัฒนาผลิตภัณฑ์และเทคโนโลยี%') AND (r.mlocation = 'ry')
GROUP BY r.mstaffno, r.sacnameTh, DATEPART(MONTH, d.createdDate) ,DATEPART(YEAR, d.createdDate), r.mtitleTh, r.mnameTh, r.surnameTh, r.memail").ToList();

                return result;

            }
        }

        public static List<ReportSB> GetReportSB(int month, SearchCiteria searchCiteria)
        {
            using (var dc = new KSSDBDataContext())
            {
                
                var allhr = from doc in dc.tblEDMSHRs
                            select doc;
                var allSec = (from k in dc.tblSections join 
                             hr in dc.tblEDMSHRs on k.sacnameTh equals hr.sacnameTh
                             select new
                                        {
                                            sectionNam = k.sacnameTh,
                                            localtion = k.location,
                                            hr.mfacName,
                                            hr.depnameTh
                                        }).Distinct();
                if (!string.IsNullOrEmpty(searchCiteria.Location) && searchCiteria.Location.ToLower() != "all")
                {
                    allhr = allhr.Where(hr => hr.mlocation == searchCiteria.Location.ToLower());
                    allSec = allSec.Where(s => s.localtion == searchCiteria.Location.ToLower());
                }
                var SB = from doc in dc.tblDocs
                         join edm in allhr on doc.createdBy equals edm.mstaffno
                         select new { doc, edm };
                if (month != -1)
                {
                    SB = SB.Where(m => m.doc.createdDate.Month == month);
                }

                if (!string.IsNullOrEmpty(searchCiteria.CreatedByName))
                {
                    SB = SB.Where(d => d.doc.createdByName.Contains(searchCiteria.CreatedByName));
                }
                if (!string.IsNullOrEmpty(searchCiteria.CreatedBy))
                {
                    SB = SB.Where(d => d.doc.createdBy.Contains(searchCiteria.CreatedBy));
                }
                if (!string.IsNullOrEmpty(searchCiteria.DepartmentName))
                {
                    SB = SB.Where(d => d.doc.departmentName.Contains(searchCiteria.DepartmentName));
                }
                if (!string.IsNullOrEmpty(searchCiteria.Title))
                {
                    SB = SB.Where(d => d.doc.title.Contains(searchCiteria.Title));
                }
                if (!string.IsNullOrEmpty(searchCiteria.Cash))
                {
                    decimal? cash = Convert.ToDecimal(searchCiteria.Cash);
                    SB = SB.Where(d => d.doc.cash == cash);
                }
                if (!string.IsNullOrEmpty(searchCiteria.Location) && searchCiteria.Location.ToLower() != "all")
                    {
                        SB = SB.Where(d => d.edm.mlocation == searchCiteria.Location.ToLower());
                    }
                if (searchCiteria.Datefrom.HasValue && searchCiteria.Datefrom.Value > DateTime.MinValue)
                {
                    SB = SB.Where(p => p.doc.createdDate >= searchCiteria.Datefrom);
                }

                if (searchCiteria.Dateto.HasValue && searchCiteria.Dateto.Value > DateTime.MinValue)
                {
                    SB = SB.Where(p => p.doc.createdDate < searchCiteria.Dateto);
                }
                var sbGroup = SB.GroupBy(e => e.edm.mstaffno).Select( i => new ReportSB
                                                                         {
                                                                             Doccount = i.Count(i2=>i2.doc.id != null),
                                                                             MFacName = i.First(i2 => i2.edm.mfacName != null).edm.mfacName,
                                                                             MdepName = i.First(i2 => i2.edm.depnameTh != null).edm.depnameTh,
                                                                             MName = i.First(i2 => i2.edm.mnameTh != null).edm.mnameTh,
                                                                             MSurname = i.First(i2 => i2.edm.surnameTh != null).edm.surnameTh,
                                                                             SacnameTh = i.First(i2 => i2.edm.sacnameTh != null).edm.sacnameTh,
                                                                             Mstaffno = i.Key
                                                                         }).ToList();
                foreach (var reportSb in sbGroup)
                {
                    var titles = dc.tblDocs.Where(i => i.createdBy == reportSb.Mstaffno);
                    int i2 = 1;
                    foreach (var tblDoc in titles)
                    {
                        reportSb.TitleSummary += i2 + ". " + tblDoc.title + ", ";
                    }
                }

                foreach (var tblSection in allSec)
                {
                    if (sbGroup.Any(i=>i.SacnameTh != tblSection.sectionNam))
                    {
                        sbGroup.Add(new ReportSB{SacnameTh = tblSection.sectionNam,
                        MFacName = tblSection.mfacName,
                        MdepName = tblSection.depnameTh});
                    }
                }

                return sbGroup;

            }
        }

        public static bool IsRequireApprove1(string username)
        {
            using (var dc = new KSSDBDataContext())
            {
                try
                {
                    var result = dc.tblDocs.Count(i => i.approver1.Equals(username) && i.status == 0) > 0;
                    return result;
                }
                catch (Exception ex)
                {
                    return false;
                }
                
            }
        }
        public static bool IsRequireApprove2(string username)
        {
            using (var dc = new KSSDBDataContext())
            {
                try
                {
                    var docNotDone = dc.tblDocs.Where(d => d.approver2.Equals(username) && (d.status < 20 && d.status != 11));
                    var result = docNotDone.Count(i => i.approver2.Equals(username) && i.status == 10 || (i.approver1.Equals(i.approver2)) && (i.status == 10 || i.status == 0)) > 0;
                    return result;

                }
                catch (Exception ex)
                {
                    return false;
                }

            }
        }
        public static string GetDocByApprover1(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    if (string.IsNullOrEmpty(username))
                    {
                        var aView = serializer.Serialize(string.Empty);
                        return aView;
                    }
                    else
                    {
                        var doc = (from docs in dataContext.tblDocs
                                   where docs.approver1 == username && docs.status == 00 
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();

                        var aView = serializer.Serialize(doc);
                        return aView;
                    }
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }
        public static string GetDocByApprover2(string username)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    if (string.IsNullOrEmpty(username))
                    {
                        var aView = serializer.Serialize(string.Empty);
                        return aView;
                    }
                    else
                    {
                        var doc = (from docs in dataContext.tblDocs
                                   where docs.approver2 == username && docs.status == 10
                                   join u in dataContext.tblUsers on docs.approver1 equals u.UserName
                                   join u2 in dataContext.tblUsers on docs.approver2 equals u2.UserName
                                   select new TblKDoc
                                   {
                                       approved1Date = docs.approved1Date,
                                       approved2Date = docs.approved1Date,
                                       approvedQMDate = docs.approvedQMDate,
                                       approver1 = u.FirstName + " " + u.Lastname,
                                       approver2 = u2.FirstName + " " + u2.Lastname,
                                       cash = docs.cash,
                                       createdBy = docs.createdBy,
                                       createdByName = docs.createdByName,
                                       createdDate = docs.createdDate,
                                       autoId = docs.autoId,
                                       departmentName = docs.departmentName,
                                       doctypeId = docs.doctypeId,
                                       id = docs.id,
                                       status = docs.status,
                                       score = docs.score,
                                       title = docs.title,
                                       tqm = docs.tqm
                                   }).ToList();

                        var aView = serializer.Serialize(doc);
                        return aView;
                    }
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }
        public static string GetDocByTqm()
        {
            //var edmContext = new EDMSDBDataContext();
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var qmlocation =
                        dataContext.tblEDMSHRs.Single(u => u.mstaffno.Equals(System.Web.HttpContext.Current.User.Identity.Name)).mlocation;
                    //var edm = edmContext.tblEDMs.ToList();
                    var doc = from ds in dataContext.tblDocs
                               join hrdb in dataContext.tblEDMSHRs  on ds.createdBy equals hrdb.mstaffno
                              where
                                  ds.status == 20 &&
                                  System.Web.HttpContext.Current.User.Identity.Name != ds.createdBy && hrdb.mlocation == qmlocation
                                  
                              select ds;
                    //var doc = from d in doc1
                    //          join e in edm on d.createdBy equals e.mempid
                    //          where e.msublocation != qmsub
                    //          select d;
                    
                    

                        var aView = serializer.Serialize(doc);
                        return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }
        public static string GetDocPropByDocID(string docId)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    var doc = (from props in dataContext.docPropertyDatas
                               where props.docId == docId
                               select props).ToList();

                    var aView = serializer.Serialize(doc);
                    return aView;
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

            }
        }

        public static Result AddDocTag(List<tblDocTag> docTag)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    string deleteProp =
                    "DELETE FROM tblDocTag WHERE docID ='" + docTag[0].docID + "'";
                    dataContext.ExecuteCommand(deleteProp);
                    dataContext.tblDocTags.InsertAllOnSubmit(docTag);
                    dataContext.SubmitChanges();
                    
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = "system",
                        logType = 1
                    });
                    return new Result(false, ex.Message);
                }
                return new Result(true);
            }
        }
        public static string AddDoc(tblDoc newDoc, List<docPropertyData> propDatas)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    dataContext.tblDocs.InsertOnSubmit(newDoc);
                    dataContext.docPropertyDatas.InsertAllOnSubmit(propDatas);
                    dataContext.SubmitChanges();

                    var log = new StringBuilder();
                    log.Append("เพิ่มเอกสารเลขที่" + newDoc.id + " โดย " + System.Web.HttpContext.Current.User.Identity.Name);
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            log.ToString(),
                        username = newDoc.createdBy,
                        logType = 0
                    });

                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = newDoc.createdBy,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string AddDoc(tblDoc newDoc)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    dataContext.tblDocs.InsertOnSubmit(newDoc);
                    dataContext.SubmitChanges();

                    var log = new StringBuilder();
                    log.Append("เพิ่มเอกสารเลขที่" + newDoc.id + " โดย " + System.Web.HttpContext.Current.User.Identity.Name);
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            log.ToString(),
                        username = newDoc.createdBy,
                        logType = 0
                    });

                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = newDoc.createdBy,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string AddDocProperties(List<docPropertyData> propDatas, out List<docPropertyData> reustProp)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    dataContext.docPropertyDatas.InsertAllOnSubmit(propDatas);
                    dataContext.SubmitChanges();
                }
                catch (Exception ex)
                {
                    reustProp = null;
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                reustProp = propDatas;
                return serializer.Serialize(new Result(true));
            }
        }
        public static string EditDocProperties(List<docPropertyData> propDatas)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    foreach (var docPropertyData in propDatas)
                    {
                        docPropertyData oldProp =
                                           dataContext.docPropertyDatas.Single(prop => prop.docId == docPropertyData.docId && prop.id == docPropertyData.id);
                        if (oldProp.dataNvarchar != null && oldProp.dataNvarchar != docPropertyData.dataNvarchar)
                        {
                            oldProp.dataNvarchar = docPropertyData.dataNvarchar;
                        }
                        if (oldProp.dataDate != null && oldProp.dataDate != docPropertyData.dataDate)
                        {
                            oldProp.dataDate = docPropertyData.dataDate;
                        }
                        if (oldProp.dataDecimal != null && oldProp.dataDecimal != docPropertyData.dataDecimal)
                        {
                            oldProp.dataDecimal = docPropertyData.dataDecimal;
                        }
                        if (oldProp.dataInt != null && oldProp.dataInt != docPropertyData.dataInt)
                        {
                            oldProp.dataInt = docPropertyData.dataInt;
                        }
                        if (oldProp.dataNtext != null && oldProp.dataNtext != docPropertyData.dataNtext)
                        {
                            oldProp.dataNtext = docPropertyData.dataNtext;
                        }
                    }
                    dataContext.SubmitChanges();

                }
                catch (Exception ex)
                {
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string DeleteProperties(docPropertyData propDatas)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    string deleteProp =
                    "DELETE FROM docPropertyData WHERE id =" + propDatas.id ;
                    dataContext.ExecuteCommand(deleteProp);

                    //var log = new StringBuilder();
                    //log.Append("เพิ่มข้อมูลที่เอกสาร เลขที่" + docid + " โดย " + newDoc.createdByName);
                    //KSSMaster.AddLog(new tblLog()
                    //{
                    //    details =
                    //        log.ToString(),
                    //    username = newDoc.createdBy,
                    //    logType = 0
                    //});

                }
                catch (Exception ex)
                {
                    //KSSMaster.AddLog(new tblLog()
                    //{
                    //    details =
                    //        ex.Message,
                    //    username = newDoc.createdBy,
                    //    logType = 1
                    //});
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string EditDoc(tblDoc editDoc, List<docPropertyData> propDatas)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    tblDoc oldDoc =
                                           dataContext.tblDocs.Single(doc => doc.id == editDoc.id);
                    var log = new StringBuilder();
                    log.Append("แก้เอกสารเลขที่" + editDoc.id + " โดย " + System.Web.HttpContext.Current.User.Identity.Name);
                    if (!string.IsNullOrEmpty(editDoc.title) && oldDoc.title != editDoc.title)
                    {
                        oldDoc.title = editDoc.title;
                    }
                    if (!string.IsNullOrEmpty(editDoc.approver1) && oldDoc.approver1 != editDoc.approver1)
                    {
                        oldDoc.approver1 = editDoc.approver1;
                    }
                    if (!string.IsNullOrEmpty(editDoc.approver2) && oldDoc.approver2 != editDoc.approver2)
                    {
                        oldDoc.approver2 = editDoc.approver2;
                    }
                    if (oldDoc.status != editDoc.status)
                    {
                        if (editDoc.status == 11)
                        {
                            log.Append("ไม่อนุมัติเอกสารเลขที่" + editDoc.id + " โดย " + System.Web.HttpContext.Current.User.Identity.Name);
                        }
                        else if (editDoc.status == 10 || editDoc.status == 20)
                        {
                            log.Append("อนุมัติเอกสารเลขที่" + editDoc.id + " โดย" + System.Web.HttpContext.Current.User.Identity.Name);
                        }
                        else if (editDoc.status == 30 )
                        {
                            tblPayCash cash = new tblPayCash{ docID = oldDoc.id, PayCash = oldDoc.cash ?? editDoc.cash ?? 0, PayDate = DateTime.Now, UserName = oldDoc.createdBy};
                            CashHelper.AddCash(cash);
                            log.Append("เอกสารเลขที่" + editDoc.id + " ผ่านการอนุมัติจาก TQM โดย" + System.Web.HttpContext.Current.User.Identity.Name);
                        }
                        oldDoc.status = editDoc.status;
                    }
                    if (editDoc.approved1Date != null)
                    {
                        oldDoc.approved1Date = editDoc.approved1Date;
                    }
                    if (editDoc.approved2Date != null)
                    {
                        oldDoc.approved2Date = editDoc.approved2Date;
                    }
                    if (editDoc.approvedQMDate != null)
                    {
                        oldDoc.approvedQMDate = editDoc.approvedQMDate;
                    }
                    if (editDoc.score !=null && oldDoc.score != editDoc.score)
                    {
                        oldDoc.score = editDoc.score;

                    }
                    if (editDoc.cash != null && oldDoc.cash != editDoc.cash)
                    {
                        oldDoc.cash = editDoc.cash;
                    }
                    string deleteprops = string.Empty;
                    int deleteI = 1;
                    foreach (var item in propDatas)
                    {
                        if (deleteI < propDatas.Count)
                        {
                            deleteprops += " propertyName = " +  "'" +item.propertyName +"'" + " or";
                        }
                        else
                        {
                            deleteprops += " propertyName = " + "'" + item.propertyName + "'";
                        }
                        deleteI++;
                    }
                    string deletePropData =
                   "DELETE FROM docPropertyData WHERE docId ='" + editDoc.id + "' and (" + deleteprops + ")";
                    dataContext.ExecuteCommand(deletePropData);
                    dataContext.docPropertyDatas.InsertAllOnSubmit(propDatas);

                    dataContext.SubmitChanges();
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            log.ToString(),
                        username = editDoc.createdBy,
                        logType = 0
                    });

                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = editDoc.createdBy,
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }

                return serializer.Serialize(new Result(true));
            }
        }
        public static string DeleteDoc(string docID)
        {
            using (var dataContext = new KSSDBDataContext())
            {
                var serializer = new JavaScriptSerializer();
                try
                {
                    tblDoc oldDoc = dataContext.tblDocs.Single(doc => doc.id == docID);
                    string deleteTag =
                    "DELETE FROM tblDoc WHERE id ='" + docID + "'";
                    dataContext.ExecuteCommand(deleteTag);
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            "ลบแบบฟอร์ม " + oldDoc.title + " ID=" + docID,
                        username = System.Web.HttpContext.Current.Request.LogonUserIdentity.Name ?? "admin",
                        logType = 0
                    });
                }
                catch (Exception ex)
                {
                    KSSMaster.AddLog(new tblLog()
                    {
                        details =
                            ex.Message,
                        username = System.Web.HttpContext.Current.Request.LogonUserIdentity.Name ?? "admin",
                        logType = 1
                    });
                    return serializer.Serialize(new Result(false, ex.Message));
                }
                return serializer.Serialize(new Result(true));
            }
        }
    }
}
